import pandas as pd
import numpy as np
import os
from typing import Dict, List
import re
from utils.mysql_util import MySQLUtil

# 表名翻译映射
TABLE_NAME_TRANSLATIONS = {
    '历史基因': 'historical_gene',
    '技艺': 'craftsmanship',
    '用途基因': 'usage_gene',
    '故事基因': 'story_gene',
    '意义基因': 'meaning_gene',
    '文物_器型基因': 'artifact_shape_gene',
    '器型基因类别表': 'shape_gene_category',
    '文物_颜色基因': 'artifact_color_gene',
    '颜色基因类别表': 'color_gene_category',
    '纹样组合创新': 'pattern_combination_innovation',
    '纹样基因归类': 'pattern_gene_classification',
    '文物_纹样基因': 'artifact_pattern_gene',
    '纹样基因类别表': 'pattern_gene_category',
    '文物_材质基因': 'artifact_material_gene',
    '材质基因类别表': 'material_gene_category',
    '基因类别阶元表': 'gene_category_hierarchy',
    '文物多角度照片表': 'artifact_multi_angle_photos',
    '文物本体表': 'artifact_main',
    '文物本体表结构': 'artifact_main_structure'
}

# 表头翻译映射
HEADER_TRANSLATIONS = {
    # 通用字段
    '序号': 'serial_number',
    '编号': 'code',
    '名称': 'name',
    '描述': 'description',
    '备注': 'remarks',
    '创建时间': 'created_at',
    '更新时间': 'updated_at',
    '状态': 'status',
    
    # 文物相关
    '文物名称': 'artifact_name',
    '文物编号': 'artifact_code',
    '年代': 'period',
    '朝代': 'dynasty',
    '类别': 'category',
    '材质': 'material',
    '器型': 'shape',
    '纹样': 'pattern',
    '颜色': 'color',
    
    # 基因相关
    '基因编码': 'gene_code',
    '基因名称': 'gene_name',
    '基因类型': 'gene_type',
    '基因描述': 'gene_description',
    '基因分类': 'gene_category',
    '基因层级': 'gene_level',
    '基因特征': 'gene_feature',
    
    # 器型相关
    '器型特点': 'shape_feature',
    '器型分类': 'shape_category',
    '器型描述': 'shape_description',
    
    # 纹样相关
    '纹样特点': 'pattern_feature',
    '纹样类型': 'pattern_type',
    '纹样描述': 'pattern_description',
    '纹样位置': 'pattern_location',
    
    # 颜色相关
    '颜色特点': 'color_feature',
    '颜色类型': 'color_type',
    '颜色描述': 'color_description',
    
    # 材质相关
    '材质特点': 'material_feature',
    '材质类型': 'material_type',
    '材质描述': 'material_description',
    
    # 照片相关
    '照片路径': 'photo_path',
    '照片描述': 'photo_description',
    '拍摄角度': 'photo_angle',
    
    # 其他字段
    '用途': 'usage',
    '工艺': 'technique',
    '历史背景': 'historical_background',
    '文化意义': 'cultural_significance',
    '保存状态': 'preservation_status',
    '收藏单位': 'collection_unit',
    '出土地点': 'excavation_site',
    '出土时间': 'excavation_time'
}

def clean_column_name(column: str) -> str:
    """清理和标准化列名"""
    # 移除空格和特殊字符
    clean_name = re.sub(r'[^\w\s]', '', column.strip())
    # 转换为小写
    clean_name = clean_name.lower()
    # 用下划线替换空格
    clean_name = clean_name.replace(' ', '_')
    return clean_name

def translate_table_name(chinese_name: str) -> str:
    """将中文表名翻译为英文"""
    # 移除文件扩展名（如果有）
    base_name = os.path.splitext(chinese_name)[0]
    
    # 清理表名中的特殊字符
    clean_name = re.sub(r'[^\w\u4e00-\u9fff]', '_', base_name)
    
    # 查找预定义的翻译
    if clean_name in TABLE_NAME_TRANSLATIONS:
        return TABLE_NAME_TRANSLATIONS[clean_name]
    
    # 如果没有预定义翻译，将中文转换为拼音或使用其他转换方法
    # 这里简单地将未翻译的名称转换为小写并用下划线连接
    return re.sub(r'[^\w]', '_', clean_name.lower())

def translate_headers(df: pd.DataFrame) -> pd.DataFrame:
    """翻译和标准化表头"""
    new_columns = {}
    for col in df.columns:
        # 清理列名
        cleaned_col = clean_column_name(col)
        # 查找翻译
        if col in HEADER_TRANSLATIONS:
            new_columns[col] = HEADER_TRANSLATIONS[col]
        else:
            # 如果没有预定义翻译，使用清理后的列名
            new_columns[col] = cleaned_col
    
    return df.rename(columns=new_columns)

def get_column_type(col_name: str, dtype: str) -> str:
    """根据列名和数据类型决定SQL字段类型"""
    # 可能包含长文本的字段名关键词
    long_text_keywords = ['描述', '特点', '说明', '内容', '详情', '备注', '简介']
    
    # 检查列名是否包含长文本关键词
    if any(keyword in col_name for keyword in long_text_keywords):
        return 'TEXT'
    
    # 根据数据类型判断
    if 'int' in str(dtype):
        return 'INT'
    elif 'float' in str(dtype):
        return 'FLOAT'
    elif 'datetime' in str(dtype):
        return 'DATETIME'
    else:
        # 其他字符串类型使用较大的VARCHAR
        return 'VARCHAR(1000)'

def create_table_sql(df: pd.DataFrame, table_name: str) -> str:
    """根据DataFrame生成建表SQL"""
    columns = []
    for col in df.columns:
        # 获取适当的SQL类型
        sql_type = get_column_type(col, df[col].dtype)
        columns.append(f"`{col}` {sql_type}")
    
    return f"""
    CREATE TABLE IF NOT EXISTS `{table_name}` (
        `auto_id` INT AUTO_INCREMENT PRIMARY KEY,
        {', '.join(columns)}
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """

def clean_dataframe(df: pd.DataFrame) -> pd.DataFrame:
    """清理DataFrame中的特殊值"""
    # 将所有的numpy.nan和pandas.NaT替换为None
    df = df.replace({np.nan: None, pd.NaT: None})
    
    # 处理数值列中的无限值
    df = df.replace([np.inf, -np.inf], None)
    
    # 将所有列转换为字符串类型，除了数值类型
    for col in df.columns:
        if not np.issubdtype(df[col].dtype, np.number):
            df[col] = df[col].astype(str).replace('None', None)
            # 截断超长字符串（仅对非TEXT类型的字段）
            if get_column_type(col, df[col].dtype) == 'VARCHAR(1000)':
                df[col] = df[col].apply(lambda x: x[:1000] if x is not None else x)
    
    return df

def import_to_mysql():
    # 创建MySQL工具类实例
    mysql_util = MySQLUtil()
    
    try:
        # 获取所有Excel文件
        excel_dir = 'data/extracted_sheets'
        excel_files = [f for f in os.listdir(excel_dir) if f.endswith('.xlsx')]
        
        for excel_file in excel_files:
            print(f"处理文件: {excel_file}")
            
            try:
                # 读取Excel文件
                file_path = os.path.join(excel_dir, excel_file)
                df = pd.read_excel(file_path)
                
                # 处理表头
                df = translate_headers(df)
                
                # 清理数据
                df = clean_dataframe(df)
                
                # 生成英文表名
                table_name = translate_table_name(excel_file)
                print(f"转换表名: {excel_file} -> {table_name}")
                
                # 删除已存在的表（如果存在）
                drop_sql = f"DROP TABLE IF EXISTS `{table_name}`"
                mysql_util.execute_update(drop_sql)
                
                # 创建表
                create_sql = create_table_sql(df, table_name)
                mysql_util.execute_update(create_sql)
                
                # 准备插入数据
                columns = list(df.columns)
                columns_str = ', '.join([f"`{col}`" for col in columns])
                placeholders = ', '.join(['%s'] * len(columns))
                insert_sql = f"INSERT INTO `{table_name}` ({columns_str}) VALUES ({placeholders})"
                
                # 将DataFrame转换为值列表
                values = df.values.tolist()
                
                # 批量插入数据
                if values:
                    # 每次插入1000条记录
                    batch_size = 1000
                    for i in range(0, len(values), batch_size):
                        batch_values = values[i:i + batch_size]
                        mysql_util.execute_many(insert_sql, batch_values)
                        print(f"表 {table_name}: 已导入 {min(i + batch_size, len(values))}/{len(values)} 条记录")
                    print(f"表 {table_name} 导入完成: 共 {len(values)} 条记录")
                else:
                    print(f"表 {table_name} 没有数据需要导入")
                
            except Exception as e:
                print(f"处理文件 {excel_file} 时出错: {str(e)}")
                print("继续处理下一个文件...")
                continue
        
        print("所有数据导入完成！")
        
    except Exception as e:
        print(f"导入过程中出现错误: {str(e)}")
        import traceback
        print(traceback.format_exc())

if __name__ == "__main__":
    import_to_mysql()
